/*
 * Created on Oct 22, 2003
 */
package org.vectrics.recruiter.job;

import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;

import org.apache.log4j.Logger;
import org.vectrics.DataException;
import org.vectrics.common.db.JdbcTarget;
import org.vectrics.common.db.ProcessException;

public class FindMatchingJobsJdbc implements JdbcTarget {
    private transient static Logger log = Logger.getLogger(FindMatchingJobsJdbc.class);

    private static SimpleDateFormat sqlDateFormat = new SimpleDateFormat("yyyy-MM-dd");

    private HashSet jobRatingList = new HashSet();

    private HashSet contendingJobIds = new HashSet();

    private String sqlQry = "";

    HashSet searchWordList = null;

    private String searchWords = null;

    public FindMatchingJobsJdbc(final String searchWords, final String locationId, final String function,
            final long startTime) {
        if (log.isDebugEnabled()) {
            log.debug("findMatchingJobs() called.  SearchWords = " + searchWords);
            log.debug("findMatchingJobs() called.  location = " + locationId);
        }
        this.searchWords = searchWords;
        searchWordList = SearchUtil.parseSearchWords(searchWords);
        SearchUtil.addImpliedSearchWords(searchWordList);

        log.debug("findMatchingJobs() - finding jobs with any search word");
        Iterator searchWordIter = searchWordList.iterator();
        // Ok to not give search word - just show all jobs that match the other
        // criteria

        log.debug("findMatchingJobs() - building 'where' clause of SQL statement");
        StringBuffer whereClause = new StringBuffer();
        if (searchWordIter.hasNext()) {
            while (searchWordIter.hasNext()) {
                String word = (String) searchWordIter.next();
                if (whereClause.length() > 0) {
                    whereClause.append(" OR");
                } else {
                    whereClause.append(" (");
                }
                // whereClause.append(" (s.phrase='" + word + "' )");
                whereClause.append(" (lower(j.description) like '%" + word.toLowerCase() + "%')");
                whereClause.append(" OR (lower(j.company_info) like '%" + word.toLowerCase() + "%')");
                whereClause.append(" OR (lower(j.title) like '%" + word.toLowerCase() + "%')");
                whereClause.append(" OR (lower(j.req_skills) like '%" + word.toLowerCase() + "%')");

            }
            whereClause.append(")");
        }
        if (whereClause.toString().length() > 0) {
            whereClause.append(" and");
        }

        log.debug("findMatchingJobs() - adding location and job id to where clause");
        if (locationId != null && locationId.length() > 0) {
            whereClause.append(" (j.location_id like '" + locationId + "%')");
        }

        log.debug("findMatchingJobs() - adding function to where clause");
        if (function != null) {
            if (function.length() > 0) {
                log.debug("findMatchJobs() - function added to query: " + function);
                whereClause.append(" and (j.function_code = '" + function + "')");
            } else {
                log.debug("findMatchJobs() - function was blank - search"
                        + " query will not include function in where clause");

            }
        } else {
            log.debug("findMatchJobs() - function was not specified");
        }

        log.debug("findMatchingJobs() - adding date limits to where clause");
        if (startTime > 0) {
            String startTimeStr = sqlDateFormat.format(new Date(startTime));
            whereClause.append(" and (j.date_posted >= '" + startTimeStr + "')");
        }

        log.debug("findMatchingJobs() - finding jobs with search words");
        sqlQry = "select distinct j.id" + " from job_post j";
        String where = whereClause.toString().trim();
        if (where.length() > 0) {
            sqlQry += " where" + whereClause.toString();
        }

        if (log.isDebugEnabled()) {
            log.debug("findMatchingJobs() - qry to find jobs that match any" + " search word qry = " + sqlQry);
        }

    }

    public Collection getJobRatingList() {
        return jobRatingList;
    }

    public void processResults(final ResultSet resultSet) throws ProcessException {
        try {
            log.debug("findMatchingJobs() - processing query results.");
            while (resultSet.next()) {
                log.debug("findMatchingJobs() - reading next record for first" + " search word job match.");
                String jobId = resultSet.getString("id");
                contendingJobIds.add(jobId);
            }
            log.debug("findMatchingJobs() - number of jobs = " + contendingJobIds.size());
        } catch (java.sql.SQLException se) {
            log.error("findMatchingJobs() - exception walking results.", se);
        }
        try {
            Iterator iter = contendingJobIds.iterator();
            while (iter.hasNext()) {
                Long jobId = (Long) iter.next();
                int rating = JobSearchUtil.findSearchRatingForJob(jobId, searchWords);

                JobSearchRating jobRating = new JobSearchRating();
                jobRating.setJobId(jobId);
                jobRating.setRating(rating);
                jobRating.setDatePosted(JobSearchUtil.findJobDate(jobId));
                jobRatingList.add(jobRating);
            }
        } catch (DataException de) {
            log.error("DataException processing results", de);
            throw new ProcessException("DataException processing results", de);
        }

    }

    public String getQuery() {
        return sqlQry;
    }

}
